<?php

namespace app\helper;

use PDO;

class Schema {

    static private $_instance = null;
    static private $_newSchemaConnention = null;
    static private $_oldSchemaConnention = null;
    static private $_defaultConnention = null;
    static private $_newSchema = null;
    static private $_oldSchema = null;

    private function __construct($newSchema, $oldSchema) {
        static::$_newSchema = $newSchema;
        static::$_oldSchema = $oldSchema;
        file_put_contents(DIFFSQL, sprintf('USE `%s`;', static::$_oldSchema) . PHP_EOL);
    }

    /**
     * @return PDO
     */
    static public function getDefaultConnection() {
        if (!static::$_defaultConnention) {
            $dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', 'mysql', HOST, PORT);
            static::$_defaultConnention = new PDO($dsn, USER, PASS);
            static::$_defaultConnention->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return static::$_defaultConnention;
    }

    /**
     * @return PDO
     */
    static protected function getNewSchemaConnection() {
        if (!static::$_newSchemaConnention) {
            $dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', static::$_newSchema, HOST, PORT);
            static::$_newSchemaConnention = new PDO($dsn, USER, PASS);
            static::$_newSchemaConnention->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return static::$_newSchemaConnention;
    }

    /**
     * @return PDO
     */
    static protected function getOldSchemaConnection() {
        if (!static::$_oldSchemaConnention) {
            $dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', static::$_oldSchema, HOST, PORT);
            static::$_oldSchemaConnention = new PDO($dsn, USER, PASS);
            static::$_oldSchemaConnention->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return static::$_oldSchemaConnention;
    }

    /**
     * @return Schema
     */
    static public function getInstance($newSchema, $oldSchema) {
        if (!static::$_instance) {
            static::$_instance = new static($newSchema, $oldSchema);
        }
        return static::$_instance;
    }

    public function createSchema($schema) {
        $create_schema = sprintf('DROP DATABASE IF EXISTS `%s`;CREATE DATABASE `%s` DEFAULT CHARSET=\'utf8mb4\';USE `%s`;', $schema, $schema, $schema);
        static::getDefaultConnection()->exec($create_schema);
    }

    public function make() {
        list($newTable, $oldTable) = static::getTable();
        foreach ($oldTable as $table) {
            if (!in_array($table, $newTable)) {
                static::writeSql(sprintf(PHP_EOL . 'DROP TABLE `%s`.`%s`;'.PHP_EOL, static::$_oldSchema, $table));
            }
        }
        foreach ($newTable as $table) {
            if (!in_array($table, $oldTable)) {
                static::makeTable($table);
                continue;
            }
            $subChange = static::makeColumn($table);
            $subChange .= static::makeIndex($table);
            if ($subChange) {
                $alterTable = sprintf('ALTER TABLE `%s` ', $table) . rtrim($subChange, ',');
                static::writeSql(PHP_EOL . $alterTable . ';' . PHP_EOL);
            }
        }

        return true;
    }

    static protected function makeTable($table) {
        $connection = self::getNewSchemaConnection();
        $sql = "SHOW CREATE TABLE $table;";
        $create_table = $connection->query($sql, PDO::FETCH_COLUMN, 1)->fetch();
        static::writeSql(PHP_EOL . $create_table . ';' . PHP_EOL);
    }

    static protected function makeColumn($table) {
        list($newTableColumn, $oldTableColumn) = static::getColumn($table);
        $subChange = '';
        foreach ($oldTableColumn as $name => $row) {
            if (!isset($newTableColumn[$name])) {
                $subChange .= sprintf(PHP_EOL . '   DROP COLUMN `%s` ,', $name);
            }
        }

        foreach ($newTableColumn as $name => $row) {
            $collate = $row['Collation'] ? 'COLLATE \'' . $row['Collation'] . '\'' : '';
            $nullAble = (strtolower($row['Null']) === 'no') ? 'NOT NULL' : 'NULL';
            $default = static::getColumnDefault($row);
            $extra = $row['Extra'] ?: '';
            $comment = $row['Comment'] ? 'COMMENT \'' . $row['Comment'] . '\'' : '';
            if (empty($oldTableColumn[$name])) {
                $subChange .= sprintf(PHP_EOL . '   ADD COLUMN `%s` %s %s %s %s %s %s,', $name, $row['Type'], $collate, $nullAble, $default, $extra, $comment);
            } elseif ($row != $oldTableColumn[$name]) {
                $subChange .= sprintf(PHP_EOL . '   CHANGE `%s` `%s` %s %s %s %s %s %s,', $name, $name, $row['Type'], $collate, $nullAble, $default, $extra, $comment);
            }
        }
        return $subChange;
    }

    static protected function makeIndex($table) {
        list($newTableIndex, $oldTableIndex) = static::getIndex($table);
        $subChange = '';
        foreach ($oldTableIndex as $indexName => $row) {
            if (!isset($newTableIndex[$indexName])) {
                $subChange .= static::getDropIndexSql($row);
            }
        }
        foreach ($newTableIndex as $indexName => $row) {
            if (empty($oldTableIndex[$indexName])) {
                $subChange .= static::getAddIndexSql($row);
            } elseif ($row != $oldTableIndex[$indexName]) {
                $subChange .= static::getDropIndexSql($row);
                $subChange .= static::getAddIndexSql($row);
            }
        }
        return $subChange;
    }

    static protected function getIndex($table) {
        $sql = sprintf('SHOW INDEX FROM `%s`;', $table);
        $newTableIndex = $oldTableIndex = [];
        $new_list = static::getNewSchemaConnection()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        foreach ($new_list as $row) {
            $indexName = $row['Key_name'];
            unset($row['Privileges'], $row['Seq_in_index'], $row['Sub_part'], $row['Packed'], $row['Null'], $row['Cardinality']);
            $newTableIndex[$indexName][] = $row;
        }

        $old_list = static::getOldSchemaConnection()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        foreach ($old_list as $row) {
            $indexName = $row['Key_name'];
            unset($row['Privileges'], $row['Seq_in_index'], $row['Sub_part'], $row['Packed'], $row['Null'], $row['Cardinality']);
            $oldTableIndex[$indexName][] = $row;
        }

        return [$newTableIndex, $oldTableIndex];
    }

    static protected function getColumn($table) {
        $sql = sprintf('SHOW FULL COLUMNS FROM `%s`;', $table);
        $newTableColumn = $oldTableColumn = [];
        $new_list = static::getNewSchemaConnection()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        foreach ($new_list as $row) {
            $columnName = $row['Field'];
            unset($row['Privileges'], $row['Key']);
            $newTableColumn[$columnName] = $row;
        }

        $old_list = static::getOldSchemaConnection()->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        foreach ($old_list as $row) {
            $columnName = $row['Field'];
            unset($row['Privileges'], $row['Key']);
            $oldTableColumn[$columnName] = $row;
        }
        return [$newTableColumn, $oldTableColumn];
    }

    static protected function getTable() {
        $table_sql = 'SHOW TABLES;';
        $newTables = static::getNewSchemaConnection()->query($table_sql, PDO::FETCH_COLUMN, 0)->fetchAll();
        $oldTables = static::getOldSchemaConnection()->query($table_sql, PDO::FETCH_COLUMN, 0)->fetchAll();
        return [$newTables, $oldTables];
    }

    static protected function writeSql($sql) {
        file_put_contents(DIFFSQL, $sql, FILE_APPEND);
    }

    static protected function getColumnDefault($row) {
        if (is_null($row['Default'])) {
            return '';
        } elseif (preg_match('/^CURRENT_TIMESTAMP/i', $row['Default'])) {
            return 'DEFAULT ' . $row['Default'];
        } else {
            return 'DEFAULT \'' . $row['Default'] . '\'';
        }
    }

    static protected function getAddIndexSql($row) {
        if (count($row) === 1 && $row[0]['Key_name'] == 'PRIMARY') {
            return '';
        } elseif (count($row) >= 1 && $row[0]['Key_name'] != 'PRIMARY') {
            $unique = ($row[0]['Non_unique'] == 0) ? 'UNIQUE' : '';
            $index_type = $row[0]['Index_type'];
            $keyname = $row[0]['Key_name'];
            $column = [];
            foreach ($row as $r) {
                $column[] = '`' . $r['Column_name'] . '`';
            }
            $columns = implode(',', $column);
            return sprintf(PHP_EOL . '  ADD %s KEY `%s` (%s) USING %s,', $unique, $keyname, $columns, $index_type);
        }
    }

    static protected function getDropIndexSql($row) {
        if (count($row) === 1 && $row[0]['Key_name'] == 'PRIMARY') {
            return '';
        } elseif (count($row) >= 1 && $row[0]['Key_name'] != 'PRIMARY') {
            $indexName = $row[0]['Key_name'];
            return sprintf(PHP_EOL . '  DROP KEY `%s` ,', $indexName);
        }
    }

}
